package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/*
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
*/

import dbPool.ConnectionDAO;
import beans.Customer;

public class CustomerDAO {

	ConnectionDAO conn = new ConnectionDAO();
    Connection con = null;
    Statement stmt = null;
    
    static {
    	try{
    		new dbPool.JDCConnectionDriver("com.mysql.jdbc.Driver","jdbc:mysql://localhost/virtualreel", "root", "root"); 
    	}catch(Exception e){
    		System.out.println("Connection UNSUCCESSFULL !!!");
    		e.printStackTrace();
    	}
    }
    
    
    public Connection getConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:jdc:jdcpool");
    }
    
    
    
    public int createCustomer(Customer cBean){
    	try{
    		con = getConnection();
    		Statement stmt = con.createStatement();
    		String query = "INSERT INTO `customer`(c_id, c_first_name, c_last_name, c_email, c_address, c_city, c_state, c_zipcode, c_premium) VALUES('"+cBean.getC_Id()+"','"+cBean.getC_First_Name()+"','"+cBean.getC_Last_Name()+"','"+cBean.getC_Email()+"','"+cBean.getC_Address()+"','"+cBean.getC_City()+"','"+cBean.getC_State()+"','"+cBean.getC_Zipcode()+"','"+cBean.getC_Premium()+"');";
    		stmt.executeUpdate(query);
    		return 0;
    	}
    	catch(Exception se){
    		Logger.getLogger(CustomerDAO.class.getName()).log(Level.SEVERE, "save failed", se);
    		return 1;
    	}
    	finally {
    		try {
				con.close();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				System.out.println("Could not close connection in Finally !");
				e.printStackTrace();
			}
    	}
    }
    
    public Customer retrieveCustomer(String C_Id, String Column_Name){
    	
    	Customer cBeanOne = new Customer();
    	
    	try{
    		con = getConnection();
    		Statement stmt = con.createStatement();
    		String query = "SELECT "+Column_Name+" FROM `customer` WHERE `c_id` LIKE '"+C_Id+"';";
    		ResultSet rs = stmt.executeQuery(query);
    		while(rs.next()){
    			cBeanOne.setC_Id(rs.getString("c_id"));
    			cBeanOne.setC_First_Name(rs.getString("c_first_name"));
    			cBeanOne.setC_Last_Name(rs.getString("c_last_name"));
    			cBeanOne.setC_Email(rs.getString("c_email"));
    			cBeanOne.setC_Address(rs.getString("c_address"));
    			cBeanOne.setC_City(rs.getString("c_city"));
    			cBeanOne.setC_State(rs.getString("c_state"));
    			cBeanOne.setC_Zipcode(rs.getString("c_zipcode"));
    			cBeanOne.setC_Premium(rs.getInt("c_premium"));
    		}
    		return cBeanOne;
    	}
    	catch(Exception se){
    		Logger.getLogger(CustomerDAO.class.getName()).log(Level.SEVERE, "save failed", se);
    		return cBeanOne;
    	}
    	finally {
    		try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    	}
    }
    
    public int updateCustomer(String C_Id, String key, String value){
    	try{
    		con = getConnection();
     		Statement stmt = con.createStatement();
    		String query = "UPDATE `customer` SET  `"+key+"` =  '"+value+"' WHERE  `customer`.`c_id` =  '"+C_Id+"';";
    		stmt.executeUpdate(query);
    		return 0;
    	}
    	catch(Exception se){
    		Logger.getLogger(CustomerDAO.class.getName()).log(Level.SEVERE, "save failed", se);
    		return 1;
    	}
    	finally {
    		try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    	}
    }
    
    public int deleteCustomer(String C_Id) {
    	try{
    		con = getConnection();
    		Statement stmt = con.createStatement();
    		String query = "DELETE FROM `customer` WHERE `customer`.`c_id` = '"+C_Id+"';";
    		stmt.executeUpdate(query);
    		return 0;
    	}
    	catch(Exception se){
    		Logger.getLogger(CustomerDAO.class.getName()).log(Level.SEVERE, "save failed", se);
    		return 1;
    	}
    	finally {
    		try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    	}
    }
    
    public Customer[] retrieveAllCustomers(){
    	
    	Customer[] cBeanThree = null;
    	
    	try{
    		con = getConnection();
    		Statement stmt = con.createStatement();
    		String query = "SELECT * FROM `customer`;";
    		ResultSet rs = stmt.executeQuery(query);
    		//Customer cBeanOne = new Customer();
    		ArrayList<Customer> cBeanTwo = new ArrayList<Customer>();
    		while(rs.next()){
    			Customer cBeanOne = new Customer();
    			cBeanOne.setC_Id(rs.getString("c_id"));
    			cBeanOne.setC_First_Name(rs.getString("c_first_name"));
    			cBeanOne.setC_Last_Name(rs.getString("c_last_name"));
    			cBeanOne.setC_Email(rs.getString("c_email"));
    			cBeanOne.setC_Address(rs.getString("c_address"));
    			cBeanOne.setC_City(rs.getString("c_city"));
    			cBeanOne.setC_State(rs.getString("c_state"));
    			cBeanOne.setC_Zipcode(rs.getString("c_zipcode"));
    			cBeanOne.setC_Premium(rs.getInt("c_premium"));
    			cBeanTwo.add(cBeanOne);
    		}
    		cBeanThree = new Customer[cBeanTwo.size()];
    		cBeanTwo.toArray(cBeanThree);
    		return cBeanThree;
    	}
    	catch(Exception se){
    		Logger.getLogger(CustomerDAO.class.getName()).log(Level.SEVERE, "save failed", se);
    		return cBeanThree;
    	}
    	finally {
    		try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    	}
	}
    
    public Customer[] retrieveAllCustomers(String key, String value){
    	
    	Customer[] cBeanThree = null;
    	
    	try{
    		con = getConnection();
    		Statement stmt = con.createStatement();
    		String query = "SELECT * FROM `customer` WHERE `"+key+"` = '"+value+"';";
    		ResultSet rs = stmt.executeQuery(query);
    		ArrayList<Customer> cBeanTwo = new ArrayList<Customer>();
    		while(rs.next()){
    			Customer cBeanOne = new Customer();
    			cBeanOne.setC_Id(rs.getString("c_id"));
    			cBeanOne.setC_First_Name(rs.getString("c_first_name"));
    			cBeanOne.setC_Last_Name(rs.getString("c_last_name"));
    			cBeanOne.setC_Email(rs.getString("c_email"));
    			cBeanOne.setC_Address(rs.getString("c_address"));
    			cBeanOne.setC_City(rs.getString("c_city"));
    			cBeanOne.setC_State(rs.getString("c_state"));
    			cBeanOne.setC_Zipcode(rs.getString("c_zipcode"));
    			cBeanOne.setC_Premium(rs.getInt("c_premium"));
    			cBeanTwo.add(cBeanOne);
    		}
    		cBeanThree = new Customer[cBeanTwo.size()];
    		cBeanTwo.toArray(cBeanThree);
    		return cBeanThree;
    	}
    	catch(Exception se){
    		Logger.getLogger(CustomerDAO.class.getName()).log(Level.SEVERE, "save failed", se);
    		return cBeanThree;
    	}
    	finally {
    		try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    	}
    }
    
    public static void main(String args[]){
    	
    	/*Customer cBean = new Customer();
    	
    	
    	//trial method one
    	cBean.setC_Id("111-00-0001");
    	cBean.setC_First_Name("Kshitij");
    	cBean.setC_Last_Name("Shah");
    	cBean.setC_Email("kshitij.shah01@students.sjsu.edu");
    	cBean.setC_Address("230 E San Salvador Street, Apt #11");
    	cBean.setC_City("San Jose");
    	cBean.setC_State("California");
    	cBean.setC_Zipcode("95112");
    	cBean.setC_Premium(0);
    	
    	CustomerDAO cdao = new CustomerDAO();
    	
    	cdao.createCustomer(cBean);*/
    	
    	/*for(int i=0; i<10; i++){
    		cBean.setC_Id("000-00-000"+(i+2));
    		if(i%2==0){
    			cBean.setC_Premium(0);
    		}
    		else {
    			cBean.setC_Premium(1);
    		}
    		cdao.createCustomer(cBean);
    	}*/
    	
    	/*//trial method two
    	Customer cBean = new Customer();
    	CustomerDAO cdao = new CustomerDAO();
    	
    	for(int i=1; i<10; i++){
    		cBean = cdao.retrieveCustomer("000-00-000"+i, "*");
        	System.out.println(cBean.getC_Id()+":"+cBean.getC_Premium());
    	}*/
    	
    	/*//trial method three
    	CustomerDAO cdao = new CustomerDAO();
    	int result = cdao.updateCustomer("000-00-0002", "c_first_name", "Rahul");
    	System.out.println(result);*/
    	
    	/*//trial method four
    	CustomerDAO cdao = new CustomerDAO();
    	int result = cdao.deleteCustomer("000-00-0008");
    	System.out.println(result);*/
    	
    }
}
